﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;

namespace MoviesDAL
{
    public static class MovieServices
    {
        public static DataTable getMoviesLearning(int userid,int limit)
        {
            MySql.Data.MySqlClient.MySqlConnection con = new MySqlConnection("server=localhost; user id=root; password=ilana1; database=likesus; pooling=false;");
            con.Open();
            MySqlDataAdapter myDataAdapter = new MySqlDataAdapter("call getMoviesLearning(@userid,@nlimit)", con);
            myDataAdapter.SelectCommand.Parameters.AddWithValue("@userid", userid);
            myDataAdapter.SelectCommand.Parameters.AddWithValue("@nlimit", limit);
            DataTable myDataSet = new DataTable("movie");
            myDataAdapter.Fill(myDataSet);
            con.Close();
            return myDataSet;
        }

        public static void linkUserMovie(int userid, int movieid, float rating)
        {
            MySql.Data.MySqlClient.MySqlConnection con = new MySqlConnection("server=localhost; user id=root; password=ilana1; database=likesus; pooling=false;");
            con.Open();
            MySqlCommand myCommand = new MySqlCommand("call linkUserMovie(@userid,@movieid,@rating)", con);
            myCommand.Parameters.AddWithValue("@userid", userid);
            myCommand.Parameters.AddWithValue("@movieid", movieid);
            myCommand.Parameters.AddWithValue("@rating", rating);
            myCommand.ExecuteNonQuery();
            con.Close();
        }

        public static void userRelationsRoutine(int userid,float requestedDistance)
        {
            MySql.Data.MySqlClient.MySqlConnection con = new MySqlConnection("server=localhost; user id=root; password=ilana1; database=likesus; pooling=false;");
            con.Open();
            MySqlCommand myCommand = new MySqlCommand("call UserRelationsRoutine(@selectedUser,@requestedDistance)", con);
            myCommand.Parameters.AddWithValue("@selectedUser", userid);
            myCommand.Parameters.AddWithValue("@requestedDistance", requestedDistance);
            myCommand.ExecuteNonQuery();
            con.Close();
        }

        public static DataTable myRatings(int userid)
        {
            MySql.Data.MySqlClient.MySqlConnection con = new MySqlConnection("server=localhost; user id=root; password=ilana1; database=likesus; pooling=false;");
            con.Open();
            MySqlDataAdapter myDataAdapter = new MySqlDataAdapter("call UserMovies(@userid)", con);
            myDataAdapter.SelectCommand.Parameters.AddWithValue("@userid", userid);
            DataTable myDataSet = new DataTable("movie");
            myDataAdapter.Fill(myDataSet);
            con.Close();
            return myDataSet;
        }

        public static DataTable friendRatings(int friendid,int myid)
        {
            MySql.Data.MySqlClient.MySqlConnection con = new MySqlConnection("server=localhost; user id=root; password=ilana1; database=likesus; pooling=false;");
            con.Open();
            MySqlDataAdapter myDataAdapter = new MySqlDataAdapter("call friendRatings(@friendid,@myid)", con);
            myDataAdapter.SelectCommand.Parameters.AddWithValue("@friendid", friendid);
            myDataAdapter.SelectCommand.Parameters.AddWithValue("@myid", myid);
            DataTable myDataSet = new DataTable("movie");
            myDataAdapter.Fill(myDataSet);
            con.Close();
            return myDataSet;
        }

        public static DataTable GetTopGroupMovies(int userid,int nlimit)
        {
            MySql.Data.MySqlClient.MySqlConnection con = new MySqlConnection("server=localhost; user id=root; password=ilana1; database=likesus; pooling=false;");
            con.Open();
            MySqlDataAdapter myDataAdapter = new MySqlDataAdapter("call getGroupTopMovies(@userid,@nlimit)", con);
            myDataAdapter.SelectCommand.Parameters.AddWithValue("@userid", userid);
            myDataAdapter.SelectCommand.Parameters.AddWithValue("@nlimit", nlimit);
            DataTable myDataSet = new DataTable("movie");
            myDataAdapter.Fill(myDataSet);
            con.Close();
            return myDataSet;
        }
    }
}
